Den här guiden hjälper dig att komma åt SLU:s Basreg-databas (basregDWH) med R och RStudio.
Du kommer att lära dig att:
En beskrivning av Rstudios användargränssnitt finns på Posits hemsida (Posit utvecklar Rstudio): https://docs.posit.co/ide/user/ide/guide/ui/ui-panes.html
Om du läser den här dokumentationen med avsikten att använda den för dataanalys, se till att du förstår hur R‑projekt fungerar: https://docs.posit.co/ide/user/ide/guide/code/projects.html
Viktigt: Användare har läsbehörighet
(read-only).
Det betyder att du kan titta på och ladda ner data, men du riskerar
inte att ändra databasen.
Du behöver:
RStudio
Official getting started guide:
https://docs.posit.co/ide/user/ide/get-started/
Åtkomst till SLU Basreg / BasregDWH
För behörighet, kontakta: Eva Rundlöf
Antingen vara på ett SLU-campus eller använda SLU:s
VPN
Så använder du VPN:
https://internt.slu.se/en/support-services/administrative-support/it/support/guider-manualer/vpn-anyconnect/
Om du har en Windows-dator från SLU verifierar ODBC normalt din identitet automatiskt vid anslutning. Linux- och Mac-användare behöver följa en separat instruktion.
Om du vill hålla ditt arbete organiserat kan du använda Git direkt i
RStudio:
https://docs.posit.co/ide/user/ide/guide/tools/version-control.html
Repo:
https://github.com/TKlingstrom/Basreg_introduction
Du kan ladda ner det på två sätt:
.Rproj-filen i RStudioHelst följer du guiden direkt i R Markdown. För att göra det öppnar
du basreg_userguide.Rmd i RStudio eller öppnar RStudio och
väljer File → Open project in new session. Den svenska
versionen ligger i en mapp och du öppnar projektfilen och markdownfilen
i den foldern istället.
Du kan också skapa ett eget R‑script (i RStudio: File → New File → R-script) och kopiera koden från HTML-filen (eller PDF:en).
Skillnaden mellan R Markdown och ett R‑script är att du i R Markdown
kan köra och köra om din kod i “chunks”. Chunks är gråmarkerade och har
en liten grön play‑pil uppe till höger. Utdata från att köra koden visas
direkt under koden. R Markdown kan också enkeltanvändas för att skapa en
PDF- eller HTML-fil. Jag rekommenderar att du följer den här guiden
direkt i R Markdown-filen eller, alternativt, öppnar HTML-filen i din
webbläsare och sedan skriver in koden i ett R‑script. I R Markdown-filen
kan du scrolla ned tills du hittar den här texten och sedan börja
handledningen nedan. Varje kodchunk måste köras minst en gång (antingen
genom att klicka på den gröna play-knappen uppe till höger i chunken,
eller genom att kopiera koden till ditt R‑script, markera den och trycka
Ctrl + Enter). Du kan ändra koden och köra den igen som
du vill; namnet till vänster om <- blir namnet på
objektet som skapas och kan nås i fliken Environment
(övre högra panelen).
Vi använder följande paket som måste installeras och laddas (kör koden nedan):
Den här chunken skapar ett anslutningsobjekt som
heter con.
Tänk på con som sättet att tala om för din
RStudio‑session att ansluta till Basreg‑servern.
Du laddar inte ner hela databasen till din
dator.
I stället skickar du förfrågningar till servern och hämtar data från
SQL‑vyer.
En vy är ett sätt att presentera information i en SQL‑server och innehåller främst observationsdata (händelser, mätningar). På grund av hur Basreg har utvecklats innehåller vyerna också vissa mindre användbara kolumner som behövs för andra sätt att interagera med Basreg.
# Connecting to the database using your R/Windows user credentials
con <- dbConnect(
odbc(),
Driver = "SQL Server",
Server = "basregdwh.db.slu.se",
Database = "basregDWH"
)
# List objects available in the apiSci schema
odbcListObjects(con, catalog = "basregDWH", schema = "apiSci")Funktionen tbl() skapar en referens till en vy (view)
som anges i parentesen.
Tack vare dbplyr kan du filtrera eller bearbeta data innan du laddar ner den. I det här fallet:
head(10) begränsar till en liten förhandsvisningcollect() laddar ner de raderna till R som en data
frame%>% är sättet du anger de olika steg som dplyr och
dbplyr ska utföra i en pipe när du filtrerar eller bearbetar
data. Om du skriver DF.Cow efter pipen visas resultatet för
dig. I fliken Environment (övre högra panelen i
RStudio) ser du också alla objekt du skapar. Om du klickar på den blå
cirkeln framför ett objekt visas datatypen, och om du klickar på
tabell-ikonen längst till höger öppnas en tabellvy med innehållet i
dataframen. Fliken Connections (till höger om
Environment) visar också alla databasscheman du kan komma åt; om du har
behörighet kan du klicka på den blå cirkeln och se vad som finns i
schemat. Vyerna vi kommer att arbeta med finns under basregDWH →
apiSci och börjar med “fact”.
DF.Cow <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleView")) %>%
head(10) %>%
collect()
DF.CowProva att byta objektnamn i koden ovan, till exempel:
"factCattleView" till"factCattleReproductionView"Många Basreg-vyer är glesa (sparse).
Det betyder att de flesta rader bara har några få ifyllda kolumner
och många NA.
Filtrering hjälper dig att välja ut bara meningsfulla rader.
Exempel:
!is.na(Calving) betyder: behåll bara rader där Calving
finnsDryOff får du
sinläggningshändelser (dry-off)DF.Reproduction <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
filter(!is.na(Calving)) %>%
collect()
DF.ReproductionDu kan kombinera filter.
I filter() betyder flera villkor:
villkor 1 OCH villkor 2 OCH villkor 3
Här väljer vi:
DF.Reproduction <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
filter(
!is.na(Calving),
StartDate >= as.Date("2021-01-01"),
StartDate <= as.Date("2021-01-31")
) %>%
collect()
DF.ReproductionIbland vill du ha antingen:
I R:
| betyder ELLER& betyder OCH (kommatecken
fungerar också som du såg ovan)Här väljer vi:
DF.Reproduction <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
filter(
(!is.na(Calving) | !is.na(DryOff)),
StartDate >= as.Date("2021-01-01"),
StartDate <= as.Date("2021-01-31")
) %>%
collect()
DF.ReproductionI Basreg-vyer finns det ofta många kolumner. Funktionen
select() behåller bara de kolumner du anger. Allt annat tas
bort från det som laddas ner.
Här återanvänder vi samma fråga som i Steg 5 (Calving eller DryOff under januari 2021), men hämtar bara de viktigaste kolumnerna.
DF.Reproduction <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
filter(
(!is.na(Calving) | !is.na(DryOff)),
StartDate >= as.Date("2021-01-01"),
StartDate <= as.Date("2021-01-31")
) %>%
select(Farm, SE_Number, StartDate, Calving, CalvingEase, DryOff) %>%
collect()
DF.ReproductionNu kommer vi att bearbeta vyn innan vi laddar ner den från servern.
I chunken nedan tar vi bort kolumnerna Calving,
DryOff och StartDate och skapar i stället (med
mutate()):
CalvingDate som innehåller datumet för kalvningen (från
StartDate)DryOffDate som innehåller datumet för
sinläggningen/dry-off (från StartDate)Det gör datamängden lättare att tolka efter nedladdning.
DF.Reproduction <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
filter(
(!is.na(Calving) | !is.na(DryOff)),
StartDate >= as.Date("2021-01-01"),
StartDate <= as.Date("2021-01-31")
) %>%
mutate(
# mutate() creates new columns (or modifies existing ones)
# Read more here: https://dplyr.tidyverse.org/reference/mutate.html
CalvingDate = if_else(!is.na(Calving), StartDate, as.Date(NA)),
DryOffDate = if_else(!is.na(DryOff), StartDate, as.Date(NA))
) %>%
select(Farm, SE_Number, CalvingDate, DryOffDate, CalvingEase) %>%
collect()
DF.ReproductionReproduktionsvyn talar om när en kalvning skedde, men den visar inte direkt vilken avkomma som föddes vid den händelsen.
För att koppla kalvningen till avkommans identitet kombinerar vi två vyer:
factCattleReproductionView innehåller
kalvningsinformation och kalvningsdatumfactCattleView innehåller avkommans identitet och
moderns identitetI factCattleView lagras moderns SE-nummer i kolumnen
Mother.
Vi kommer att:
Farm, SE_Number,
CalvingDate och CalvingEase från
factCattleReproductionViewSE_Number (avkomma), Mother och
BirthDate från factCattleViewSE_Number (moder) = MotherCalvingDate = BirthDateSE_Number till Offspring# IMPORTANT:
# In this step we do NOT download (collect) the full views into R.
# Instead, dbplyr keeps the objects as "lazy" SQL queries that remain on the Basreg server.
# This means filter(), transmute() and inner_join() are translated into SQL and run server-side.
# Only when we call collect() at the end will the result be downloaded into R.
# Running computations server-side is appropriate when it reduces the amount of data you need to download. Running large computations serverside
# should however be avoided as it may impact other users.
#
# Read more here:
# - dbplyr basics (lazy queries): https://dbplyr.tidyverse.org/articles/dbplyr.html
# - dbplyr translation to SQL: https://dbplyr.tidyverse.org/articles/translation.html
# 1) Prepare the calving events table (still server-side, NOT collected)
DF.ReproductionEvents <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
filter(
!is.na(Calving),
StartDate >= as.Date("2021-01-01"),
StartDate <= as.Date("2021-01-31")
) %>%
transmute(
Farm = Farm,
SE_Number = SE_Number, # Mother identity in reproduction view
CalvingDate = StartDate, # Calving date is stored in StartDate
CalvingEase = CalvingEase
)
# 2) Prepare the cattle view with offspring information (still server-side, NOT collected)
DF.Cattle <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleView")) %>%
transmute(
Offspring = SE_Number, # Offspring identity in cattle view
Mother = Mother, # Mother identity in cattle view
BirthDate = BirthDate
)
# 3) Join the two server-side objects in SQL Server and sort from first born to last born
DF.CalvingWithOffspring <- DF.ReproductionEvents %>%
inner_join(
DF.Cattle,
by = c("SE_Number" = "Mother", "CalvingDate" = "BirthDate")
) %>%
arrange(CalvingDate) %>% # arrange() sorts the output by date (oldest first)
select(Farm, SE_Number, Offspring, CalvingDate, CalvingEase) %>%
collect() # collect() runs the SQL query and downloads ONLY the final joined result into R
# 4) List any calving events that did not find an offspring match
DF.UnmatchedCalvings <- DF.ReproductionEvents %>%
anti_join(
DF.Cattle,
by = c("SE_Number" = "Mother", "CalvingDate" = "BirthDate")
) %>%
arrange(SE_Number, CalvingDate) %>%
collect()
DF.CalvingWithOffspring #This table will contain all cases where we now have a Mother and a SE_Number for the calf.DF.UnmatchedCalvings #This table will contain all cases where we could not find a Cow born on the right day with a mother giving birth that day.Nu använder vi kalvningstabellen som vi skapade i Steg 8 för att hitta mjölkningsposter efter varje kalvning.
För varje moder (SE_Number) och varje
CalvingDate i kalvningstabellen väljer vi rader från
factCattleMilkingView där mjölkningsdatumet
(StartDate) ligger inom 365 dagar efter
kalvningsdatumet.
För att göra detta effektivt håller vi arbetet
server-side och joinar vyerna i SQL Server. Först när
vi använder collect() laddas det slutliga resultatet
ner.
# IMPORTANT:
# We recreate the calving table as a server-side query (not collected),
# then join it to the milking view on the SQL server.
# This avoids looping in R and avoids downloading large views.
#
# Read more here:
# - dbplyr joins: https://dbplyr.tidyverse.org/articles/two-table.html
# - dbplyr SQL translation: https://dbplyr.tidyverse.org/articles/translation.html
# 1) Calving table (server-side, NOT collected)
DF.CalvingWithOffspring_db <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
filter(
!is.na(Calving),
StartDate >= as.Date("2021-01-01"),
StartDate <= as.Date("2021-01-31")
) %>%
transmute(
Farm = Farm,
SE_Number = SE_Number, # Mother
CalvingDate = StartDate,
CalvingEase = CalvingEase
) %>%
inner_join(
con %>% tbl(in_catalog("basregDWH", "apiSci", "factCattleView")) %>%
transmute(
Offspring = SE_Number,
Mother = Mother,
BirthDate = BirthDate
),
by = c("SE_Number" = "Mother", "CalvingDate" = "BirthDate")
) %>%
select(Farm, SE_Number, Offspring, CalvingDate, CalvingEase)
# 2) Milking view (server-side, NOT collected)
DF.Milking <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleMilkingView"))
# 3) Join calvings to milking records and keep only milking within 365 days after each calving
DF.MilkingAfterCalving <- DF.CalvingWithOffspring_db %>%
inner_join(DF.Milking, by = "SE_Number") %>% # match milking records to the mother
filter(
StartDate >= CalvingDate,
StartDate < sql("DATEADD(day, 365, CalvingDate)") #This is where the 365 day limit is kept
) %>%
arrange(SE_Number, CalvingDate, StartDate) %>% # sort from earliest to latest
collect() # download only the filtered result to R
DF.MilkingAfterCalvingNu sammanfattar vi mjölkningsposterna som vi valde i Steg 9.
Vi grupperar datamängden efter CalvingEase och
beräknar:
StartDate)TotalYield)Eftersom kor kan mjölkas flera gånger per dag kan tabellen
factCattleMilkingView innehålla flera rader per ko och dag.
I så fall är TotalYield oftast avkastningen per mjölkning,
inte den totala dagsavkastningen. För att få en korrekt dagsavkastning
summerar vi först alla mjölkningar inom samma dag för varje ko och
beräknar sedan den genomsnittliga dagsavkastningen.
För att bättre beskriva variation redovisar vi även median och kvartiler (25% och 75%).
# This step uses group_by() and summarise() to calculate statistics.
# Read more here:
# - group_by(): https://dplyr.tidyverse.org/reference/group_by.html
# - summarise(): https://dplyr.tidyverse.org/reference/summarise.html
#
# IMPORTANT:
# In factCattleMilkingView there can be multiple rows per day (multiple milkings).
# TotalYield is then usually the yield per milking, not per day.
# To calculate DAILY yield, we first sum TotalYield within each day for each cow.
# 1) Calculate daily total yield per cow (sums multiple milkings on the same date)
DF.DailyMilkPerCow <- DF.MilkingAfterCalving %>%
group_by(CalvingEase, SE_Number, StartDate) %>%
summarise(
DailyTotalYield = sum(TotalYield, na.rm = TRUE), # sum yield across milkings that day
MilkingsPerDay = n(), # number of milkings that day
.groups = "drop"
)
DF.DailyMilkPerCow# 2) Summarise per cow (within each CalvingEase group)
DF.MilkingPerCow <- DF.DailyMilkPerCow %>%
group_by(CalvingEase, SE_Number) %>%
summarise(
MilkingDays = n_distinct(StartDate), # number of days with milking records
AvgDailyYieldCow = mean(DailyTotalYield, na.rm = TRUE),# average DAILY yield per cow
.groups = "drop"
)
DF.MilkingPerCow# 3) Summarise per CalvingEase group (mean, median, quartiles)
DF.SummaryByCalvingEase <- DF.MilkingPerCow %>%
group_by(CalvingEase) %>%
summarise(
Cows = n_distinct(SE_Number),
AvgMilkingDaysPerCow = mean(MilkingDays, na.rm = TRUE),
MedianMilkingDaysPerCow = median(MilkingDays, na.rm = TRUE),
MilkingDays_Q25 = quantile(MilkingDays, 0.25, na.rm = TRUE),
MilkingDays_Q75 = quantile(MilkingDays, 0.75, na.rm = TRUE),
AvgDailyYield = mean(AvgDailyYieldCow, na.rm = TRUE),
MedianDailyYield = median(AvgDailyYieldCow, na.rm = TRUE),
DailyYield_Q25 = quantile(AvgDailyYieldCow, 0.25, na.rm = TRUE),
DailyYield_Q75 = quantile(AvgDailyYieldCow, 0.75, na.rm = TRUE),
.groups = "drop"
)
DF.SummaryByCalvingEaseNu kommer vi att plotta daglig mjölkavkastning för varje ko i datamängden.
I stället för linjer (som kan bli svårtolkade när många kor
överlappar) plottar vi varje mjölkningsdag som en punkt.
Punkterna färgas efter CalvingEase.
Observera att steg 1–3 är ganska repetitiva exempel på join/slagningar, medan steg 4 är det nya momentet med själva plottningen.
# We plot milk yield by Days in Milk (DIM) instead of calendar date.
# ggplot2 is used for plotting
# Read more here: https://ggplot2.tidyverse.org/
# DIM = number of days since calving for each cow.
# This makes cows comparable even if they calved on different dates.
# IMPORTANT:
# DF.DailyMilkPerCow currently only contains StartDate (milking date), not CalvingDate.
# We therefore re-create a calving table server-side and join it to the daily milk table,
# then calculate DIM in R after collect().
# 1) Calving table (server-side, not collected)
DF.CalvingWithOffspring_db <- con %>%
tbl(in_catalog("basregDWH", "apiSci", "factCattleReproductionView")) %>%
filter(
!is.na(Calving),
StartDate >= as.Date("2021-01-01"),
StartDate <= as.Date("2021-01-31")
) %>%
transmute(
Farm = Farm,
SE_Number = SE_Number,
CalvingDate = StartDate,
CalvingEase = CalvingEase
)
# 2) Daily milk per cow per date (R object from Step 10)
# Make sure StartDate is Date class
DF.DailyMilkPerCow <- DF.DailyMilkPerCow %>%
mutate(StartDate = as.Date(StartDate))
# 3) Join daily milk to calving table and calculate Days in Milk (DIM)
DF.DailyMilkWithDIM <- DF.DailyMilkPerCow %>%
inner_join(
DF.CalvingWithOffspring_db %>% collect(),
by = c("SE_Number", "CalvingEase")
) %>%
mutate(
CalvingDate = as.Date(CalvingDate),
DIM = as.integer(StartDate - CalvingDate) # days since calving
) %>%
filter(DIM >= 0, DIM <= 365) # keep the first 365 days after calving
# 4) Plot dots (easier to interpret than lines when many cows overlap)
ggplot(
DF.DailyMilkWithDIM,
aes(x = DIM, y = DailyTotalYield, colour = CalvingEase)
) +
geom_point(alpha = 0.5, size = 1) +
scale_y_continuous(limits = c(0, NA)) + # Y axis starts at 0
labs(
title = "Daily milk yield after calving (Days in Milk)",
x = "Days in Milk (days since calving)",
y = "Daily milk yield (sum of TotalYield per day)",
colour = "CalvingEase"
) +
theme_minimal()Nu sparar vi mjölkningsdatamängden till din dator som en CSV-fil. Du kan tänka dig att vi senare kan undersöka en lämplig avgränsning för laktationens slut, eftersom vi i plottningen ser att flera djur redan har påbörjat sin nästa laktation.
Efter att ha sparat stänger vi databasanslutningen. Det är god praxis och frigör resurser både på din dator och på Basreg-servern.
# Save the milking data as a CSV file in your working directory
# The file will be saved in the folder returned by getwd()
# Read more here: https://www.rdocumentation.org/packages/utils/topics/write.csv
write.csv(DF.MilkingAfterCalving, "DF_MilkingAfterCalving.csv", row.names = FALSE)
# Close the database connection when you are done
# Using dbIsValid() avoids errors if the connection is already closed
# Read more here:
# - dbDisconnect(): https://dbi.r-dbi.org/reference/dbDisconnect.html
# - dbIsValid(): https://dbi.r-dbi.org/reference/dbIsValid.html
if (DBI::dbIsValid(con)) DBI::dbDisconnect(con)